package com.jsmtr.rms.service.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.jsmtr.rms.entity.*;
import com.jsmtr.rms.dao.DaoService;
import com.jsmtr.rms.service.IProjectService;
import com.jsmtr.rms.utils.BeanTools;
import com.jsmtr.rms.utils.EnumTools;
import com.jsmtr.rms.utils.db.DbUtil;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

@Service
public class ProjectService implements IProjectService {

    @Resource
    private DaoService dao;

    public void setDao(DaoService dao) {
        this.dao = dao;
    }

    /**
     * 增加查询判断
     */
    @Override
    public Map<String, Object> getProjectSql(int page, int perpage, ProjectInfo project,int state, String key) throws Exception {
        StringBuffer queryHQL = new StringBuffer("from ProjectInfo ");
        StringBuffer countHQL = new StringBuffer("select count(*) from ProjectInfo ");
        StringBuffer condition = new StringBuffer("where (pname like '%" + key + "%') ");
      //  StringBuffer condition = new StringBuffer();
        if (state != -1)
            condition.append("and state='" + state + "'");

        queryHQL.append(condition).append(" order by adddate desc");
        countHQL.append(condition);

        return DbUtil.pageQuery(page, perpage, queryHQL.toString(), countHQL.toString(), dao);
    }

    @Override
    public Map<String, Object> getProjects(int page, int perpage, ProjectInfo project) throws Exception {
        StringBuffer queryHQL = new StringBuffer("from ProjectInfo p where 1=1 ");
        StringBuffer countHQL = new StringBuffer("select count(*) from ProjectInfo p where 1=1 ");
        StringBuffer condition = new StringBuffer();
        condition.append(BeanTools.hqlWhere(project, "p"));

        queryHQL.append(condition).append(" order by p.startdate desc");
        countHQL.append(condition);

        return DbUtil.pageQuery(page, perpage, queryHQL.toString(), countHQL.toString(), dao);
    }

    @Override
    public ProjectInfo getpProjectById(String id) throws Exception {
        return (ProjectInfo) dao.findUnique("from ProjectInfo where id='" + id + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<ProjectInfoFlow> getflowbypid(String pid) throws Exception {
        return dao.find("from ProjectInfoFlow where pid='" + pid + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<ProjectInfoRegion> getRegionbypid(String pid) throws Exception {
        return dao.find("from ProjectInfoRegion where pid='" + pid + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<ProjectInfoFlow> getAllFlowDept(String pid, int fid) throws Exception {
        return dao.find("from ProjectInfoFlow where pid='" + pid + "' and fid='" + fid + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<ProjectInfoFlow> getThisFlowDept(String pid, String deptcode) throws Exception {
        return dao.find("from ProjectInfoFlow where pid='" + pid + "' and deptcode='" + deptcode + "' order by forder");
    }

    @Override
    public ProjectInfoFlow getflow(String pid, int forder) throws Exception {
        return (ProjectInfoFlow) dao.findUnique("from ProjectInfoFlow where pid='" + pid + "' and forder='" + forder + "' order by forder ");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<ProjectInfoJobs> getjobsbypid(String pid) throws Exception {
        return dao.find("from ProjectInfoJobs where pid='" + pid + "'");
    }

    @Override
    public ProjectInfoFlow getFolwById(String fid) throws Exception {
        return (ProjectInfoFlow) dao.findUnique("from ProjectInfoFlow where id='" + fid + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<ProjectInfo> getProjectInfos(int type) throws Exception {
        if (type == -1) {
            return dao.find("from ProjectInfo order by adddate desc ");
        } else {
            return dao.find("from ProjectInfo where recruittype='" + type + "' order by adddate desc ");
        }
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<ProjectInfo> getProjectByPlan(int type) throws Exception {
        return dao.find("from ProjectInfo where recruittype!='" + type + "'");
    }

    @Override
    public List getProjectInfoYears() throws Exception {
        return dao.executeSQLQuery(" select to_char(t.adddate,'YYYY') as  addyear from PROJECT_INFO t where t.recruittype!=4 group by to_char(t.adddate,'YYYY') order by to_char(t.adddate,'YYYY') desc ");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<ProjectInfo> getProjectByYear(String year) throws Exception {
        return dao.find("from ProjectInfo where to_char(adddate,'YYYY')='" + year + "'");
    }

    @Override
    public int getZZFlowUserCount(String pid, String fkey, String fvalue) {
        try {
            String sql = "select count(1) from HRZZUser where pid='" + pid
                    + "' and " + fkey + "='" + fvalue + "'";
            return ((Long) dao.findUnique(sql)).intValue();
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }
    }

    @Override
    public int getGXFlowUserCount(String pid, String fkey, String fvalue) {
        try {
            String sql = "select count(1) from HRGXUser where pid='" + pid
                    + "' and " + fkey + "='" + fvalue + "'";
            return ((Long) dao.findUnique(sql)).intValue();
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Object[]> getProjectByDid(String did) throws Exception {
        String hql = "select p.planname,p.pname,p.recruittype,p.state,j.zpcount,j.rzpcount,p.id from ProjectInfo p,ProjectInfoJob j where p.id=j.pid and j.did='" + did + "'";
        return dao.find(hql);
    }

    @Override
    public ProjectInfo getProjectInfo(String fid) throws Exception {
        return (ProjectInfo) dao.findUnique("from ProjectInfo where id=(select pid from ProjectInfoFlow where id='" + fid + "')");
    }

    @Override
    public ProjectInfoJobs getProjectInfoJob(String id) throws Exception {
        return (ProjectInfoJobs) dao.findUnique("from ProjectInfoJobs where id='" + id + "')");
    }

    @Override
    public ProjectInfoJobs getProjectInfoJob(String pid, String jobname) throws Exception {
        return (ProjectInfoJobs) dao.findUnique("from ProjectInfoJobs where pid='" + pid + "' and jobname='" + jobname + "')");
    }

    @Override
    public ProjectInfoJobs getProjectInfoJob(String id, String pid, String jobname) throws Exception {
        return (ProjectInfoJobs) dao.findUnique("from ProjectInfoJobs where pid='" + pid + "' and id!='" + id + "' and jobname='" + jobname + "')");
    }

    @Override
    public void endProjectByEndTime() throws Exception {
        String findSql="select count(1) from PROJECT_INFO where state="+EnumTools.ProjectState.已发布.ordinal()+" and sysdate>(enddate+1)";
       Object cresult= dao.findUniqueSql(findSql);
       if(cresult!=null&&Integer.parseInt(cresult.toString())>0){
           String updateSql=" update PROJECT_INFO set state="+ EnumTools.ProjectState.招聘结束.ordinal()+" where id in (select id from PROJECT_INFO where state="+ EnumTools.ProjectState.已发布.ordinal()+" and sysdate>(enddate+1))";
          int uresult= dao.executeSQL(updateSql);
           System.out.println("更新招聘方案状态为结束:"+uresult+"条");

       }

    }

    @Override
    public List<ProjectInfoJobtemplate> findProjectInfoJobTemplateByLike(String state,String jobName, String tempName,String type) throws Exception {
        String findHql=" from ProjectInfoJobtemplate where 1=1 ";
        Map<String,Object> params=new HashMap<>();
        if(state!=null&&state.length()>0) {
            findHql += " and state=:state ";
            params.put("state", state);
        }
        if(jobName!=null&&jobName.length()>1){
            findHql+=" and jobname like '%'||:jobname||'%'";
            params.put("jobname",jobName);
        }
        if(tempName!=null&&tempName.length()>1){
            findHql+=" and tempname like '%'||:tempname||'%'";
            params.put("tempname",tempName);
        }

        if(type!=null&&type.length()>1){
            findHql+=" and type = :type";
            params.put("type",type);
        }
        if (params.size()>0){
            return  dao.find(findHql,params);
        }else{
            return  dao.find(findHql);
        }
    }


    @Override
    public ProjectInfoFlow getNextFlow(String pid, String deptcode, String fid) throws Exception {
        StringBuilder hql = new StringBuilder("from ProjectInfoFlow where pid='" + pid + "' and deptcode='" + deptcode + "' ");
        hql.append("and forder>(select forder from ProjectInfoFlow where id='" + fid + "') order by forder");
        return (ProjectInfoFlow) dao.findFirst(hql.toString());
    }
}